from utils.DBService import sqlquery
from utils.DataUitl import getpar,getfrs

import decimal,os

KUCUN_SQL="""
WITH cte AS (
    select ts.departname,cl.cailiaoName,kc.kucun,ROW_NUMBER() over(PARTITION by ts.departname,cl.cailiaoName order by kc.createDateTime desc) as rn
from WZKuCun as kc,t_s_depart as ts,WZCaiLiaoNameDict as cl where kc.departId = ts.ID and kc.cailiaoguid = cl.cailiaoNo
and ts.ID = '{}'
{}
)
SELECT * 
FROM cte
WHERE rn = 1"""

CAILIAONO_SQL = """select cailiaoNo from WZCaiLiaoNameDict where parentNode in (select cailiaoNo from WZCaiLiaoNameDict where cailiaoName in (\'{}\'))"""

DEP_SQL = """WITH  dept
    AS (SELECT  *
        FROM   t_s_depart
        WHERE  parentdepartid = '{}'
        UNION ALL
        SELECT  d.*
        FROM   t_s_depart d
            INNER JOIN dept ON d.parentdepartid = dept.id
						 )
  SELECT id
  FROM  dept where departname not like '%监理%' order by departname"""

PAR_ID = """WITH PAR AS
(
SELECT * FROM t_s_depart WHERE ID ='{}'
UNION ALL
SELECT T1.* FROM PAR,t_s_depart as T1 WHERE PAR.parentdepartid=T1.ID
)
SELECT * FROM PAR where departname not like '%监理%'"""

TEST_SQL = """select * from t_s_depart where ID in (\'{}\')"""

def inventoryStatic(Args,user):
    print('InventoryBakTwo参数:\n\t', Args)
    depId = Args['depId']
    # depId = 'f6323b406d19fc87016d1a5b43910012'
    dates = Args['dates']
    materials = Args['materials']

    # query jinchang
    frstitle = ''
    lot = ''
    depId = '\',\''.join(depId.split(','))
    tests = sqlquery(TEST_SQL.format(depId))
    print('TESTS:\n\t', tests.__len__())
    if tests.__len__() == 0:
        depId = 'All'
    if depId == 'All':
        deps = sqlquery(DEP_SQL.format(user))
        print('SQL:\n\t', DEP_SQL.format(user))
        print(deps)
        depsid = []
        for dep in deps:
            depsid.append(dep[0])
        depId = depsid
    else:
        dei = depId.split(',')
        depsidsum = []
        for de in dei:
            deps = sqlquery(DEP_SQL.format(de))
            print('SQL:\n\t', DEP_SQL.format(de))
            print(deps)
            depsid = []
            for dep in deps:
                depsid.append(dep[0])
            if depsid.__len__()>0:
                for dep in depsid:
                    depsidsum.append(dep)
            else:
                depsidsum.append(de)
        depId = depsidsum
    materials = '\',\''.join(materials.split(','))
    print(materials)
    print('SQL:\n\t', CAILIAONO_SQL.format(materials))
    cl = sqlquery(CAILIAONO_SQL.format(materials))
    print(cl)
    if cl.__len__() > 0:
        cls = []
        for c in cl:
            cls.append(c[0])
        materials = '\',\''.join(cls)
        clWhere = 'and cl.cailiaoNo in (\'{}\') '.format(materials)
    else:
        clWhere = 'and cailiaoName like \'%{}%\' '.format(materials) if materials != 'All' else ''

    marktitle = '截止到'+dates+'库存信息如下'
    clData = {}
    resdata = []
    lottitle = ''
    headtitle = ''
    sitetitle = ''
    data = {}
    lot = {}
    site = {}
    title = {'key': 0, 'cailiao': '', 'zhongliang': None, 'cailiao1': '', 'zhongliang1': None}
    keyindex = 0
    for dep in depId:
        titleindex = 0
        kucunSql = KUCUN_SQL.format(dep, clWhere)
        print('KC_SQL:\n\t', kucunSql)
        cailiaoData = sqlquery(kucunSql)
        if cailiaoData.__len__() == 0 :
            continue
        parSql = PAR_ID.format(dep)
        print('PAR_SQL:\n\t', parSql)
        parData = sqlquery(parSql)
        for i in range(parData.__len__()):
            print(parData[i][1])
            if i == parData.__len__() - 1:
                htitle = parData[i][1]
                if htitle != headtitle:
                    headtitle = htitle
                    if len(data) == 0:
                        data = {'title': htitle, 'children': []}
                    else:
                        resdata.append(data)
                        data = {'title': htitle, 'children': []}
            elif i == 0:
                stitle = parData[i][1]
                if stitle != sitetitle:
                    sitetitle = stitle
                    if len(site) == 0:
                        site = {'title': stitle, 'data': {
                            'tableHead': [{
                                'title': '名称',
                                'dataIndex': 'cailiao',
                                'key': 'cailiao',
                            },
                                {
                                    'title': '总重量(吨)',
                                    'dataIndex': 'zhongliang',
                                    'key': 'zhongliang',
                                },
                                {
                                    'title': '名称',
                                    'dataIndex': 'cailiao1',
                                    'key': 'cailiao1',
                                },
                                {
                                    'title': '总重量(吨)',
                                    'dataIndex': 'zhongliang1',
                                    'key': 'zhongliang1',
                                }],
                            'tableBody': []
                        }}
                    else:
                        if len(title) != 0:
                            site['data']['tableBody'].append(title)
                        lot['children'].append(site)
                        site = {'title': stitle, 'data': {
                            'tableHead': [{
                                'title': '名称',
                                'dataIndex': 'cailiao',
                                'key': 'cailiao',
                            },
                                {
                                    'title': '总重量(吨)',
                                    'dataIndex': 'zhongliang',
                                    'key': 'zhongliang',
                                },
                                {
                                    'title': '名称',
                                    'dataIndex': 'cailiao1',
                                    'key': 'cailiao1',
                                },
                                {
                                    'title': '总重量(吨)',
                                    'dataIndex': 'zhongliang1',
                                    'key': 'zhongliang1',
                                }],
                            'tableBody': []
                        }}
            else:
                ltitle = parData[i][1]
                if ltitle != lottitle:
                    lottitle = ltitle
                    if len(lot) == 0:
                        lot = {'title': ltitle, 'children': []}
                    else:
                        if len(lot) != 0:
                            data['children'].append(lot)
                        lot = {'title': ltitle, 'children': []}

        for cailiao in cailiaoData:
            if os.environ.get("ENV_NAME") == '1':
                depName, clName, weight = cailiao[0], cailiao[1],cailiao[2]
                                          # decimal.Decimal(cailiao[2]).quantize(decimal.Decimal('0.00'))
            else:
                depName, clName, weight = cailiao[0], cailiao[1].encode('latin1').decode('gbk'), cailiao[2]
                                          # decimal.Decimal(cailiao[2]).quantize(decimal.Decimal('0.00'))
            print(depName, clName, weight,cailiao[2])
            if depName not in clData:
                clData[depName] = {"materials": [clName], 'weights': [weight]}
            else:
                clData[depName]['materials'].append(clName)
                clData[depName]['weights'].append(weight)
            if titleindex == 0:
                keyindex = keyindex + 1
                title['key'] = keyindex
                title['cailiao'] = clName
                title['zhongliang'] = float(cailiao[2])
                titleindex = 1
            else:
                title['cailiao1'] = clName
                title['zhongliang1'] = float(cailiao[2])
                site['data']['tableBody'].append(title)
                title = {'key':0,'cailiao':'','zhongliang':None,'cailiao1':'','zhongliang1':None}
                titleindex = 0
    if len(title) != 0:
        site['data']['tableBody'].append(title)
    if len(site) != 0:
        lot['children'].append(site)
    if len(lot) != 0:
        data['children'].append(lot)
    if len(data) != 0:
        resdata.append(data)
    return clData,resdata,marktitle


functionConf = {
    "name": "inventoryStatic",
    "description": "Query the latest inventory data of materials based on their outbound ID, time, and material name. Be careful not to disturb",
    "parameters": {
        "type": "object",
        "properties": {
            "depId": {
                "type": "string",
                "description": "Department ID, multiple departments separated by commas in English. If no department is specified for the problem, it defaults to All，eg：f6323b406b4fab1b016bacabdb9f01d3,f6323b406c1829b3016c269d3b2c0036",
            },
            "dates": {
                "type": "string",
                "description": "The time of the material, using yyyy-MM-dd format, with multiple dates separated by commas, for example:2023-07-14,2023-07-15"
            },
            "materials": {
                "type": "string",
                "description": "Item names, multiple items separated by commas, all items using 'All', such as fly ash, cement"
            }
        },
        "required": [
            "depId",
            "dates",
            "materials"
        ]
    }
}